from collections import defaultdict
from typing import List

import pandas as pd
import pymysql

# 数据库配置
db_config = {
    'host': 'rm-bp1r2fpy197t1g48l.mysql.rds.aliyuncs.com',
    'port': 3306,
    'user': 'pindao_test',
    'passwd': 'test@pindao',
    'db': 'infrabase_mdm_test',
    'charset': 'utf8mb4'
}

# 对应的表
db_table = "saas_mdm_public_area_20240820_copy1"


# 数据库存储对象
class PublicAreaObject(object):

    def __init__(self, province_name, city_name, area_id, parent_area_id, area_name, area_depths,
                 country_abbreviation, country_name, country_english_name,
                 country_phone_code, country_currency, time_zone):
        self.province_name = province_name
        self.city_name = city_name
        self.area_id = area_id
        self.parent_area_id = parent_area_id
        self.area_name = area_name
        self.area_depths = area_depths
        self.country_abbreviation = country_abbreviation
        self.country_name = country_name
        self.country_english_name = country_english_name
        self.country_phone_code = country_phone_code
        self.country_currency = country_currency
        self.time_zone = time_zone

    def __str__(self):
        return ("{"
                "\"province_name\":\"%s\",\"city_name\":\"%s\","
                "\"area_id\":\"%s\",\"parent_area_id\":\"%s\","
                "\"area_name\":\"%s\",\"area_depths\":\"%s\","
                "\"country_abbreviation\":\"%s\",\"country_name\":\"%s\",\"country_english_name\":\"%s\","
                "\"country_phone_code\":\"%s\","
                "\"country_currency\":\"%s\",\"time_zone\":\"%s\""
                "}"
                %
                (
                    self.province_name, self.city_name,
                    self.area_id, self.parent_area_id, self.area_name, self.area_depths,
                    self.country_abbreviation, self.country_name, self.country_english_name,
                    self.country_phone_code,
                    self.country_currency, self.time_zone
                )
                )

    # 该方法用于转 json 字符串
    def encode_language_object(self):
        return {
            'province_name': self.province_name,
            'city_name': self.city_name,
            'area_id': self.area_id,
            'parent_area_id': self.parent_area_id,
            'area_name': self.area_name,
            'area_depths': self.area_depths,
            'country_abbreviation': self.country_abbreviation,
            'country_name': self.country_name,
            'country_english_name': self.country_english_name,
            'country_phone_code': self.country_phone_code,
            'country_currency': self.country_currency,
            'time_zone': self.time_zone}


def read_thailand_excel_data(country_abbreviation: str, country_name: str, country_english_name: str,
                             country_phone_code: str, country_currency: str,
                             time_zone: str) -> List[PublicAreaObject]:
    # 读取 excel 表格数据，并进行遍历
    # header = 0 ，第 1 行为表字段
    pd_read_excel = pd.read_excel("file\\泰国_美国_行政区域划分.xlsx", sheet_name='泰国', header=0)
    # 遍历每一行数据
    obj_list = []
    # 根据 area_name 字段去重
    unique_objects = []
    for index, row in pd_read_excel.iterrows():
        province_name = row[0]
        city_name = row[1]
        area_name = row[2]
        public_area_obj = PublicAreaObject(province_name, city_name, None, None, area_name,
                                           None, country_abbreviation,
                                           country_name, country_english_name, country_phone_code, country_currency,
                                           time_zone)
        if area_name in unique_objects:
            continue
        unique_objects.append(area_name)
        obj_list.append(public_area_obj)
    return obj_list


# 读取数据，并保存到数据库
def read_excel_save_db():
    country_abbreviation = "TH"
    country_name = "泰国"
    country_english_name = "Thailand"
    country_phone_code = "66"
    country_currency = "THB"
    time_zone = "UTC+7"
    original_area_id = 6600000
    # 读取 Excle 数据
    obj_list = read_thailand_excel_data(country_abbreviation, country_name, country_english_name,
                                        country_phone_code, country_currency, time_zone)
    print(f'obj_list.size:{len(obj_list)}')
    # 省分组
    # 使用 default-dict 分组
    grouped_by_province = defaultdict(list)
    # 遍历对象列表并按 province_name 分组
    for obj in obj_list:
        grouped_by_province[obj.province_name].append(obj)
    # 输出按 province_name 分组结果
    for province_name, province_list in grouped_by_province.items():
        print(f"Province: {province_name}")
        province = query_by_area_name(province_name, 1, country_abbreviation)
        print(f'db_province:{province}')
        if province is None:
            # 构建一条省份数据，并插入
            province = PublicAreaObject(None, None, None, "66", province_name, 1, country_abbreviation,
                                        country_name, country_english_name, country_phone_code, country_currency,
                                        time_zone)
            save_public_area(province, original_area_id)
            province_area_id = province.area_id
        else:
            province_area_id = province.area_id
        original_area_id = province_area_id + 1
        # 根据 city_name 进行分组
        grouped_by_city = defaultdict(list)
        # 遍历对象列表并按 province_name 分组
        for obj in province_list:
            grouped_by_city[obj.city_name].append(obj)
        # 输出按 city_name 分组结果
        for city_name, city_list in grouped_by_city.items():
            print(f" - city_name: {city_name}")
            city = query_by_area_name(city_name, 2, country_abbreviation)
            print(f'db_city:{city}')
            if city is None:
                # 构建一条省份数据，并插入
                city = PublicAreaObject(None, None, None, province_area_id, city_name, 2, country_abbreviation,
                                        country_name, country_english_name, country_phone_code, country_currency,
                                        time_zone)
                save_public_area(city, original_area_id)
                city_area_id = city.area_id
            else:
                city_area_id = city.area_id
            original_area_id = city_area_id + 1
            for obj in city_list:
                print(f"   - area:{obj}")
                distinct = query_by_area_name(obj.area_name, 3, country_abbreviation)
                obj.area_depths = 3
                obj.parent_area_id = city_area_id
                if distinct is None:
                    save_public_area(obj, original_area_id)
                    distinct_id = obj.area_id
                else:
                    distinct_id = distinct.area_id
                original_area_id = distinct_id + 1


def query_by_area_name(area_name, area_depths, country_abbreviation) -> PublicAreaObject:
    """
    根据地区名称、深度和国家缩写查询并返回 PublicAreaObject 对象。

    :param area_name: 地区名称
    :param area_depths: 地区深度
    :param country_abbreviation: 国家缩写
    :return: 匹配的 PublicAreaObject 对象或 None
    """
    # 查询语句
    query_by_area_name_sql = (f"SELECT "
                              f"*  "
                              f"FROM {db_table} "
                              f"WHERE area_name = %s and area_depths = %s and country_abbreviation = %s ")
    try:
        # 创建连接
        connection = pymysql.connect(**db_config)
        with connection.cursor(pymysql.cursors.DictCursor) as cursor:
            # 执行查询
            cursor.execute(query_by_area_name_sql, (area_name, area_depths, country_abbreviation))
            # 获取查询结果
            result = cursor.fetchone()
            # 如果查询结果存在，则创建 PublicAreaObject 实例
            if result:
                # 创建 PublicAreaObject 实例
                public_area_obj = PublicAreaObject(
                    province_name=None,
                    city_name=None,
                    area_name=result['area_name'],
                    area_id=result['area_id'],
                    area_depths=result['area_depths'],
                    parent_area_id=result['parent_area_id'],
                    country_abbreviation=result['country_abbreviation'],
                    country_name=result['country_name'],
                    country_english_name=result['country_english_name'],
                    country_phone_code=result['country_phone_code'],
                    country_currency=result['country_currency'],
                    time_zone=result['time_zone'],
                )
                return public_area_obj
    finally:
        # 关闭连接
        connection.close()


def query_by_area_name_and_parent_id(area_name, area_depths, country_abbreviation, parent_area_id) -> PublicAreaObject:
    # 查询语句
    query_by_area_name_sql = (f"SELECT "
                              f"*  "
                              f"FROM {db_table} "
                              f"WHERE area_name = %s and area_depths = %s and country_abbreviation = %s and parent_area_id = %s")
    try:
        # 创建连接
        connection = pymysql.connect(**db_config)
        with connection.cursor(pymysql.cursors.DictCursor) as cursor:
            # 执行查询
            cursor.execute(query_by_area_name_sql, (area_name, area_depths, country_abbreviation, parent_area_id))
            # 获取查询结果
            result = cursor.fetchone()
            # 如果查询结果存在，则创建 PublicAreaObject 实例
            if result:
                # 创建 PublicAreaObject 实例
                public_area_obj = PublicAreaObject(
                    province_name=None,
                    city_name=None,
                    area_name=result['area_name'],
                    area_id=result['area_id'],
                    area_depths=result['area_depths'],
                    parent_area_id=result['parent_area_id'],
                    country_abbreviation=result['country_abbreviation'],
                    country_name=result['country_name'],
                    country_english_name=result['country_english_name'],
                    country_phone_code=result['country_phone_code'],
                    country_currency=result['country_currency'],
                    time_zone=result['time_zone'],
                )
                return public_area_obj
    finally:
        # 关闭连接
        connection.close()


def query_by_country_abbreviation(area_depths: object, country_abbreviation: object) -> List[PublicAreaObject]:
    # 查询语句
    query_by_condition_sql = (f"SELECT "
                              f"*  "
                              f"FROM {db_table} "
                              f"WHERE area_depths = %s and country_abbreviation = %s ")
    try:
        # 创建连接
        connection = pymysql.connect(**db_config)
        with connection.cursor(pymysql.cursors.DictCursor) as cursor:
            # 执行查询
            cursor.execute(query_by_condition_sql, (area_depths, country_abbreviation))
            # 获取查询结果
            query_list = cursor.fetchall()
            result_list = []
            for row in query_list:
                # 创建 PublicAreaObject 实例
                public_area_obj = PublicAreaObject(
                    province_name=None,
                    city_name=None,
                    area_name=row['area_name'],
                    area_id=row['area_id'],
                    area_depths=row['area_depths'],
                    parent_area_id=row['parent_area_id'],
                    country_abbreviation=row['country_abbreviation'],
                    country_name=row['country_name'],
                    country_english_name=row['country_english_name'],
                    country_phone_code=row['country_phone_code'],
                    country_currency=row['country_currency'],
                    time_zone=row['time_zone'],
                )
                result_list.append(public_area_obj)
            return result_list
    finally:
        # 关闭连接
        connection.close()


def check_area_id(area_id: int):
    check_area_id_sql = f"select area_id from {db_table} where  area_id = %s"
    try:
        # 创建连接
        connection = pymysql.connect(**db_config)
        with connection.cursor(pymysql.cursors.DictCursor) as cursor:
            # 执行查询
            loop = True
            while loop:
                cursor.execute(check_area_id_sql, area_id)
                # 获取查询结果
                result = cursor.fetchone()
                # 如果查询结果存在，则创建 PublicAreaObject 实例
                if result:
                    area_id += 1
                else:
                    loop = False
        return area_id
    finally:
        # 关闭连接
        connection.close()


def save_public_area(public_area_obj: PublicAreaObject, area_id: int):
    # 插入语句
    insert_public_area_sql = f"""
    INSERT INTO {db_table} (
        area_id, parent_area_id,area_name,area_depths, country_abbreviation,
        country_name, country_english_name, country_phone_code,
        country_currency, time_zone
    )
    VALUES (
          %s, %s, %s,%s, %s,
        %s, %s, %s, %s, %s
    )
    """

    area_id = check_area_id(area_id)
    public_area_obj.area_id = area_id

    try:
        # 创建连接
        connection = pymysql.connect(**db_config)

        with connection.cursor() as cursor:
            # 执行插入操作
            cursor.execute(insert_public_area_sql, (
                public_area_obj.area_id, public_area_obj.parent_area_id, public_area_obj.area_name,
                public_area_obj.area_depths, public_area_obj.country_abbreviation,
                public_area_obj.country_name, public_area_obj.country_english_name,
                public_area_obj.country_phone_code, public_area_obj.country_currency, public_area_obj.time_zone
            ))

            # 提交事务
            connection.commit()

        # 输出结果
        print("插入成功")

    except Exception as e:
        # 如果发生错误，回滚事务
        connection.rollback()
        print("插入失败:", e)

    finally:
        # 关闭连接
        connection.close()


# read_excel_save_db()

area = query_by_area_name('中国', 0, 'CN')
print(f"area:{area}")
